
# library(pacman)
# p_load(dplyr, ggplot2, cowplot, DBI, RSQLite) 
library(dplyr)
library(ggplot2)
library(cowplot)
library(DBI)
library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), "iso_standards.sqlite") # Attaching to database

tc_creation <- dbReadTable(con, "historical_tc_creation") %>% # Extracting dataset with when TCs were established
  mutate(year = as.numeric(year)) %>% # Making year numeric
  na.omit() # Removing NA rows

dbDisconnect(con) # Disconnecting from database

tc_plot1 <- tc_creation %>% 
  mutate(spec = ifelse(sector %in% c("Business management and innovation", 
                                     "Sustainability and environment",
                                     "Services", 
                                     "Security, safety and risk"), # If a committee is within the following sectors, it is social, otherwise technical
                       "Social", "Technical")) %>%
  group_by(spec, year) %>%
  add_count(name = "Number of technical committees established") %>% # Counting number of committees established within each spec year-wise
  ggplot(aes(year, spec, fill = `Number of technical committees established`)) + # Plotting
  geom_tile() +
  scale_fill_gradient(low = "white", high = "darkblue") +
  labs(x = "", y = "") +
  theme_classic() +
  theme(legend.position = "bottom")

tc_plot2 <- tc_creation %>% # Same procedure, but now filtering out social committees and plotting them separately
  filter(sector %in% c("Business management and innovation", 
                       "Sustainability and environment", 
                       "Services", 
                       "Security, safety and risk")) %>% 
  group_by(year) %>%
  add_count(name = "Number of technical committees established") %>%
  ggplot(aes(year, sector, fill = `Number of technical committees established`)) +
  geom_tile() +
  scale_fill_gradient(low = "white", high = "darkred") +
  theme_classic() +
  labs(x = "", y = "") +
  theme(legend.position = "bottom")

plot_grid(tc_plot1, # Aligning figures on top of each other 
          tc_plot2,
          ncol = 1,
          byrow = TRUE,
          label_size = 10,
          align = "v")
